﻿using Dapper;
using OMG.Fill.Mulberry.Model;
using OMG.Fill.Mulberry.Model.Ent;
using ProjectShare;
using System;
using System.Collections.Generic;
using System.Linq;
namespace OMG.Fill.Mulberry.Service
{
    /// <summary>
    /// 数据字典表T_Dict通用操作类
    /// </summary>
    public class DictServices:Dicts
    {
        public Pager<List<DictEntity>> GetPageList(DictQuery query)
        {
            Pager<List<DictEntity>> obj = new Pager<List<DictEntity>>();
            var paramlist = new DynamicParameters();
            string strWhereSql = "";//from 表在下面，需完善其他查询条件在这里接的写，格式为："and xxx=@xxx空格,如下注释部分代码所示："
            if (!string.IsNullOrEmpty(query.GroupName))
            {
                strWhereSql += "and GroupName = @GroupName ";
                paramlist.Add("GroupName", query.GroupName);
            }
            if (!string.IsNullOrEmpty(query.GroupCode.ToString().Trim()))
            {
                strWhereSql += "and GroupCode = @GroupCode ";
                paramlist.Add("GroupCode", query.GroupCode);
            }
            if (!string.IsNullOrEmpty(query.Status))
            {
                strWhereSql += "and Status = @Status ";
                paramlist.Add("Status", query.Status);
            }
            if (!string.IsNullOrEmpty(query.DictText.ToString().Trim()))
            {
                strWhereSql += "and DictText like @DictText ";
                paramlist.Add("DictText", "%" + query.DictText + "%");
            }
            string strFromSql = "from dbo.T_Dict where 1=1 " + strWhereSql;
            string strTotal = "select count(1)" + strFromSql + strWhereSql;
            string strOrderSql = "";//自定义完成order by语句
            string strSql = @"select * from (select *,ROW_NUMBER() over (order by Id ) as RowNumber " + strFromSql + ") as t ";
            strSql += "where t.RowNumber between  @minRow and @maxRow" + strOrderSql;
            paramlist.Add("minRow", query.MinRow);
            paramlist.Add("maxRow", query.MaxRow);
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    obj.rows = dbCon.Query<DictEntity>(strSql,paramlist).ToList();
                    obj.total = dbCon.Query<int>(strTotal,paramlist).FirstOrDefault();
                    obj.status = DefaultKey.SuccessResult;
                    obj.message = "获取成功！";
                    dbCon.Dispose();//数据库连接释放
                    return obj;
                }
            }
            catch (Exception ex)
            {
                obj.status = DefaultKey.ErrorResult;
                obj.message = ex.ToString().Trim();
                LogHelper.ErrorLog("获取数据字典表T_Dict分页列表失败", ex);
                return obj;
            }
        }

        public ResResult<List<GroupItem>> GetGroupList()
        {
            ResResult<List<GroupItem>> obj = new ResResult<List<GroupItem>>();
            string strSql = "select DISTINCT GroupName,GroupCode from dbo.T_Dict";
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    obj.model = dbCon.Query<GroupItem>(strSql).ToList();
                    obj.message = "获取数据字典表T_Dict分组列表成功";
                    obj.status = DefaultKey.SuccessResult;
                    dbCon.Dispose();//数据库连接释放
                    return obj;
                }
            }
            catch (Exception ex)
            {
                LogHelper.ErrorLog("获取数据字典表T_Dict分页列表失败", ex);
                obj.message = ex.ToString().Trim();
                obj.status = DefaultKey.ErrorResult;
                return obj;
            }
        }

        public override ResResult<List<DictEntity>> GetList()
        {
            ResResult<List<DictEntity>> obj = new ResResult<List<DictEntity>>();
            string strSql = "select * from dbo.T_Dict where status='1' order by GroupCode asc,Sort asc ";
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    obj.model = dbCon.Query<DictEntity>(strSql).ToList();
                    obj.status = DefaultKey.SuccessResult;
                    obj.message = "获取成功！";
                    dbCon.Dispose();//数据库连接释放
                    return obj;
                }
            }
            catch (Exception ex)
            {
                obj.status = DefaultKey.ErrorResult;
                obj.message = ex.ToString().Trim();
                LogHelper.ErrorLog("更新数据字典表T_Dict对象信息失败", ex);
                return obj;
            }
        }

        /// <summary>
        /// 根据分组名称，获取数据字典
        /// </summary>
        /// <returns></returns>
        public ResResult<List<Select>> GetListByGroupCode(string GroupCode)
        {
            ResResult<List<Select>> obj = new ResResult<List<Select>>();
            List<Select> list = new List<Select>();
            string strSql = "select * from dbo.T_Dict where GroupCode='" + GroupCode.ToString().Trim() + "' and status=1";
            List<DictEntity> TempList = new List<DictEntity>();
            TempList = dbCon.Query<DictEntity>(strSql).ToList();
            try
            {
                if (TempList.Count() != 0)
                {
                    foreach (var dict in TempList)
                    {
                        Select model = new Select();
                        model.label = dict.DictText;
                        model.value = dict.DictValue;
                        list.Add(model);
                    }
                    obj.model = list;
                    obj.status = DefaultKey.SuccessResult;
                    obj.message = "分组代码为:" + GroupCode + "的数据字典列表获取成功";
                }
                else
                {
                    obj.message = "分组代码为:" + GroupCode + "的数据字典列表不存在";
                    obj.status = DefaultKey.InfoResult;
                }
            }
            catch (Exception ex)
            {
                LogHelper.ErrorLog("分组代码为:" + GroupCode + "的数据字典列表获取失败", ex);
                obj.message = ex.ToString().Trim();
                obj.status = DefaultKey.ErrorResult;
            }
            return obj;
        }
        /// <summary>
        /// 根据填报年度值获取对应key
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public string GetKey(string name)
        {
            string result = "";
            string strSql = @"select * from dbo.T_Dict where GroupCode = @GroupCode and DictText=@DictText";
            try
            {

                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    result = dbCon.Query<DictEntity>(strSql, new { GroupCode = "ReportYear", DictText = name }).FirstOrDefault().DictValue;
                }
            }
            catch (Exception ex)
            {
                LogHelper.ErrorLog("根据填报年度值获取对应key失败", ex);

            }
            return result;
        }

        /// <summary>
        /// 根据品种名称获取对应key
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public string GetPZKey(string name,string GroupCode)
        {
            string result = "";
            string strSql = @"select * from dbo.T_Dict where GroupCode = @GroupCode and DictText=@DictText";
            try
            {

                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    result = dbCon.Query<DictEntity>(strSql, new { GroupCode = GroupCode, DictText = name }).FirstOrDefault().DictValue;
                }
            }
            catch (Exception ex)
            {
                LogHelper.ErrorLog("根据品种名称获取对应key失败", ex);

            }
            return result;
        }

        /// <summary>
        /// 根据key获取对应Name
        /// </summary>
        /// <param name="value"></param>
        /// <returns></returns>
        public string GetPZName(string value, string GroupCode)
        {
            string result = "";
            string strSql = @"select * from dbo.T_Dict where GroupCode = @GroupCode and DictValue=@DictValue";
            try
            {

                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    result = dbCon.Query<DictEntity>(strSql, new { GroupCode = GroupCode, DictValue = value }).FirstOrDefault().DictText;
                }
            }
            catch (Exception ex)
            {
                LogHelper.ErrorLog("根据品种key获取对应名称失败", ex);

            }
            return result;
        }
    }
}